package com.situ.mall.utils;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFCellStyle;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;

import com.situ.mall.entity.User;
import com.situ.mall.mapper.UserMapper;

@RunWith(SpringJUnit4ClassRunner.class) // 表示继承了SpringJUnit4ClassRunner类 
@ContextConfiguration(locations = { "classpath:applicationContext.xml"}) //加载spring容器

public class POITest {
	
	@Autowired
	private UserMapper userMapper;
	
	@Test
	public void POI_导出java数据至Excel2005() throws IOException{
		
		//1、创建工作簿：Workbook
		HSSFWorkbook workbook = new HSSFWorkbook();
		//2、创建工作表：Sheet
		HSSFSheet sheet = workbook.createSheet();
		//3、创建行：Row
		HSSFRow row = sheet.createRow(2-1); //java从0开始，Excel从1开始。java2=Excel3,所以java2-1在Excel才是2。
		//4、创建单元格：Cell
		HSSFCell cell = row.createCell(2-1);
		//5、写入内容
		cell.setCellValue("卢本伟6P！！！");
		
//		HSSFWorkbook workbook = new HSSFWorkbook();
//		HSSFSheet sheet = workbook.createSheet();
//		HSSFRow row = sheet.createRow(2-1);
//		HSSFCell cell = row.createCell(2-1);
//		cell.setCellValue("卢本伟6P！！！");
		
		// 输出excel到文件
		FileOutputStream fileOutputStream = new FileOutputStream("D:\\pic\\POI测试2005Excel.xls");
		workbook.write(fileOutputStream);
		workbook.close();
		fileOutputStream.close();
	}
	
	@Test
	public void POI_导出java数据至Excel2007() throws IOException{
		
		//1、创建工作簿：Workbook
		XSSFWorkbook workbook = new XSSFWorkbook();
		//2、创建工作表：Sheet
		XSSFSheet sheet = workbook.createSheet();
		//3、创建行：Row
		XSSFRow row = sheet.createRow(2-1); //java从0开始，Excel从1开始。java2=Excel3,所以java2-1在Excel才是2。
		//4、创建单元格：Cell
		XSSFCell cell = row.createCell(2-1);
		//5、写入内容
		cell.setCellValue("卢本伟6P！！！From 2007Excel");
		
		// 输出excel到文件
		FileOutputStream fileOutputStream = new FileOutputStream("D:\\pic\\POI测试2007Excel.xls");
		workbook.write(fileOutputStream);
		workbook.close();
		fileOutputStream.close();
	}
	
	@Test
	public void POI_读取Excel2007至java数据() throws IOException{
		
		//0、创建IO流 读取Excel
		FileInputStream fileInputStream = new FileInputStream("D:\\pic\\POI测试2007Excel.xls");
		//1、工作簿：Workbook，获取excel文件
		XSSFWorkbook workbook = new XSSFWorkbook(fileInputStream);
		//2、工作表：Sheet，获取第0个（Excel里是1）工作空间
		XSSFSheet sheet = workbook.getSheetAt(1-1);
		//3、行：Row，获取sheet1的第2行
		XSSFRow row = sheet.getRow(2-1); //java从0开始，Excel从1开始。java2=Excel3,所以java2-1在Excel才是2。
		//4、格：Cell，获取row2的第2单元格 （列）
		XSSFCell cell = row.getCell(2-1);
		//5、取出 数据
		String cellValue = cell.getStringCellValue();
		//6、打印
		System.out.println("单元格的值：" + cellValue);

	}
	
	@Test
	public void POI_用java制作Excel() throws IOException{
		
		XSSFWorkbook workbook = new XSSFWorkbook();
		XSSFSheet sheet = workbook.createSheet("用户列表");
		// 设置默认的列宽
		sheet.setDefaultColumnWidth(25);
		// 创建合并单元格对象
		CellRangeAddress cellRangeAddress = new CellRangeAddress(2, 4, 1, 4);
		sheet.addMergedRegion(cellRangeAddress);
		// 创建行：Row
		XSSFRow row = sheet.createRow(2);
		// 创建单元格：Cell (这个cell就是合并单元格的cell)
		XSSFCell cell = row.createCell(1);
		// 设置单元格的样式
		XSSFCellStyle style = workbook.createCellStyle();
		style.setAlignment(XSSFCellStyle.ALIGN_CENTER);// 水平居中
		style.setVerticalAlignment(XSSFCellStyle.VERTICAL_CENTER);// 垂直居中
		cell.setCellStyle(style);
		cell.setCellValue("用户列表");
		// 创建要输出的列
		String[] array = {"编号", "用户名", "手机号", "邮编"};
		XSSFRow rowHead = sheet.createRow(5);
		for (int i = 0; i < array.length; i++) {
			XSSFCell cellHead = rowHead.createCell(i + 1);
			cellHead.setCellValue(array[i]);
		}
		
		List<User> list = userMapper.selectAll();
		// 这个i是变量控制行
		for (int i = 0; i < list.size(); i++) {
			User user = list.get(i);
			XSSFRow userRow = sheet.createRow(6 + i);
			
			XSSFCell cellId = userRow.createCell(1);
			cellId.setCellValue(user.getId());
			
			XSSFCell cellName = userRow.createCell(2);
			cellName.setCellValue(user.getUsername());
			
			XSSFCell cellPhone = userRow.createCell(3);
			cellPhone.setCellValue(user.getPhone());
			
			XSSFCell cellEmail = userRow.createCell(4);
			cellEmail.setCellValue(user.getEmail());
		}
		
		
		
		
		FileOutputStream fileOutputStream = new FileOutputStream("D:\\pic\\样式Excel.xlsx");
		workbook.write(fileOutputStream);
		workbook.close();
		fileOutputStream.close();

		
	}

}
